package com.limao.DBProvider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.Szjcyh.Until.Api4;
import com.Szjcyh.Until.QueryJWModeHold;
import com.Szjcyh.Until.QueryJWModeHoldData;
import com.Szjcyh.Until.ResultModel4;
import com.Szjcyh.Until.TestXml2Json;
import com.alibaba.fastjson.JSON;
import com.limao.CacheManager.BaseDataCacheManager;
import com.limao.CacheManager.CrowdOrderCache;
import com.limao.CacheManager.CrowdSysProjectCache;
import com.limao.CacheManager.CrowdUserAuthCache;
import com.limao.CacheManager.VrGoodsCache;
import com.limao.DBDataClass.CrowdOrderDataClass;
import com.limao.DBDataClass.CrowdSysProjectClass;
import com.limao.DBDataClass.CrowdUserAuthClass;
import com.limao.DBDataClass.VrGoodsDataClass;
import com.limao.DbConnection.DbConnectionManager;
import com.limao.Model.LzPluginException;
import com.limao.Model.PublicData;
import com.limao.Tool.CacheClass;
import com.limao.Utils.OrderPayParm;
import com.limao.dataEnum.OnlinePayEnum;
import com.limao.dataEnum.OrderStateEnum;
import com.limao.push.BuyCutThread;
import com.limao.push.InviteCutThread;
import com.limao.push.SysRmbRecTypeEnum;
import com.limao.push.pushToAPPTemplate;

public class CrowdOrderProvider {

	private static class DB_order_ProviderContainer {
		private static CrowdOrderProvider instance = new CrowdOrderProvider();
	}

	public static CrowdOrderProvider getInstance() {
		return DB_order_ProviderContainer.instance;
	}

	// 定义系统表的name与操作语句
	private static final String table1 = "cd_sys_order";


	// 增加正式订单
	private static final String INSERT_SYS_ORDER = "INSERT INTO "
			+ table1
			+ " (userid,mobile,companyid,projectid,projectname,selectextra,packageprice,viplevel,days,ordernum,priceunits,frieght,totalrmb,paytype,state,isaccouts,ctime,address,smallcompanyid,istest,alipaytype,finishdays) "
			+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	

	// 用户操作 签收 --签收即完成
	private static final String UPDATE_SYS_ORDER_USER_CONFIRM = "UPDATE "+ table1+ " SET state=?,issys=1,arrivetime=? where orderid=? and userid=?";
	
	//取消订单时
	private static final String UPDATE_SYS_ORDER_USER_CONFIRM2 = "UPDATE "+ table1+ " SET state=?,canceltime=? where orderid=? and userid=?";


	



	// 初始化
	private CrowdOrderProvider() {

	}

	private CrowdOrderDataClass encapOrderData(ResultSet rs) throws Exception{
		
		CrowdOrderDataClass orderData = new CrowdOrderDataClass();
	
		orderData.orderid = rs.getString("orderid"); 						
		orderData.userid = rs.getString("userid");			
		orderData.mobile = rs.getString("mobile");
		orderData.companyid = rs.getString("companyid");
		orderData.projectid = rs.getString("projectid");
		orderData.projectname = rs.getString("projectname");
		orderData.packageprice = rs.getString("packageprice");
		orderData.viplevel = rs.getString("viplevel");
		orderData.days = rs.getString("days");
		orderData.smallcompanyid = rs.getString("smallcompanyid");
		

		orderData.ordernum = rs.getString("ordernum");
		orderData.totalrmb = rs.getString("totalrmb");
		orderData.selectextra = rs.getString("selectextra");		
		
		orderData.priceunits = rs.getString("priceunits");  
		orderData.frieght = rs.getString("frieght");
		orderData.paytype = rs.getString("paytype");
		if(!CacheClass.isEmpty(orderData.paytype))
			orderData.paytypeAbout=OnlinePayEnum.getEnumString(CacheClass.getIntValue(orderData.paytype));
		else
			orderData.paytypeAbout = "";
		
		orderData.state = rs.getString("state");				// 0 取消 1待付款 2已付款  3抵押中  4已失权益   5可申请发货  6已申请发货 7发货  8签收
		
		orderData.stateAbout = OrderStateEnum.getEnumString(CacheClass.getIntValue(orderData.state));
		
		orderData.isaccouts= rs.getString("isaccouts");
		
		orderData.ctime = rs.getString("ctime");
		orderData.ctimeAbout = CacheClass.getFormatDate(CacheClass.getLongValue(orderData.ctime),"yyyy年MM月dd日  HH:mm:ss");
		
		orderData.order_sn = orderData.orderid+"-"+CacheClass.getFormatDate(CacheClass.getLongValue(orderData.ctime), "yyyyMMddHHmmss");
		orderData.address = rs.getString("address");	
		  
		orderData.canceltime = rs.getString("canceltime");	// 取消定单时间
		orderData.starttime = rs.getString("starttime");		// 备货开始时间
		if(!CacheClass.isEmpty(orderData.starttime))
			orderData.starttimeAbout = CacheClass.getFormatDate(CacheClass.getLongValue(orderData.starttime),"yyyy年MM月dd日  HH:mm:ss");
		else
			orderData.starttimeAbout="";
		
		orderData.couriertime = rs.getString("couriertime");	// 配送开始时间
		if(!CacheClass.isEmpty(orderData.couriertime))
			orderData.couriertimeAbout = CacheClass.getFormatDate(CacheClass.getLongValue(orderData.couriertime),"yyyy年MM月dd日  HH:mm:ss");
		else
			orderData.couriertimeAbout="";		
		
		orderData.courier_sn = rs.getString("courier_sn") ;
		if(!CacheClass.isEmpty(orderData.courier_sn))
		{
			String[] courier=orderData.courier_sn.split("_");
			if(courier.length==2)
			{
				orderData.courier_url="https://m.baidu.com/from=1086k/s?word="+courier[0]+" "+courier[1]+
										"&sa=tb&ts=5415529&t_kt=0&ie=utf-8&rsv_t=692blouIXTgbHHJci0hQ9WesJ0ezvDUnJpQutYDfyDmhNSF27Icr13OFl8U&rsv_pq=17079004029315873772&ss=100&rsv_sug4=14678&inputT=9764&oq="+courier[0];
			}
		}
		orderData.arrivetime = rs.getString("arrivetime");	// 送达时间
		if(!CacheClass.isEmpty(orderData.arrivetime))
			orderData.arrivetimeAbout = CacheClass.getFormatDate(CacheClass.getLongValue(orderData.arrivetime),"yyyy年MM月dd日  HH:mm:ss");
		else
			orderData.arrivetimeAbout="";	
		
		orderData.issys = rs.getString("issys") ;
		
		
		orderData.projectimage = rs.getString("projectimage") ;
		orderData.intro = rs.getString("intro") ;
		  
		orderData.companyname = rs.getString("companyname") ;
		orderData.compaddress = rs.getString("compaddress") ;
		orderData.tel = rs.getString("tel") ;
		orderData.qq = rs.getString("qq") ;


		//设置订单的抵押  信息
		//orderData.childlist=CrowdOweProvider.getInstance().Load_CrowdOwe_ByOrderId(orderData.orderid);

		orderData.cancalAbout=CacheClass.getSYS_BaseDataCacheManager().Load_Param("cancalAbout");
		orderData.cancalAboutUrl=CacheClass.getDomain()+CacheClass.getSYS_BaseDataCacheManager().Load_Param("cancalAboutUrl");

		orderData.istest = rs.getString("istest") ;
		
		return orderData;
		
	}
		
	//&新鲜宝    增加正式订单
	public OrderPayParm Add_Order(int userid,String[] strIdArr, String[] strNumArr, String[] selectextraArr, int paytype,String address,String shengid,String cityid,int alipayType)	throws LzPluginException {

		OrderPayParm tmp=null;
		float alltotalrmb = 0;
		String goodsname="";
		
		//取用户数据
		CrowdUserAuthClass user=CrowdUserAuthCache.getInstance().getUser(userid);
		if(user==null )
		{
			throw new LzPluginException("用户不存在！");
		}

		// 数据库操作
		boolean abortTransaction = false;
		Connection con = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Date now = new Date();
		try {
			
			con = DbConnectionManager.getTransactionConnection();
			//插入地址
			String[] addresslist=address.split("_");
			pstmt = con.prepareStatement("INSERT INTO cd_user_address "
					+ "(userid,person_name,tel,shengid,cityid,address,isdef,time) VALUES (?,?,?,?,?,?,?,?)");
			pstmt.setInt(1, userid);
			pstmt.setString(2, addresslist[1]);
			pstmt.setString(3, addresslist[2]);
			pstmt.setInt(4, CacheClass.getIntValue(shengid));
			pstmt.setInt(5, CacheClass.getIntValue(cityid));
			pstmt.setString(6, addresslist[0]);
			
			pstmt.setInt(7, 1);
			pstmt.setLong(8, now.getTime());
			pstmt.execute();
			
			for(int i=0;i<strIdArr.length;i++)
			{
				int projectid=CacheClass.getIntValue(strIdArr[i]);
				int num=CacheClass.getIntValue(strNumArr[i]);
				String selectextra=selectextraArr[i].trim();
				float totalrmb = 0;
				
				CrowdSysProjectClass project=CrowdSysProjectProvider.getInstance().load_Goods_Info_ById2(projectid);
				if(project==null)
				{
					throw new LzPluginException("商品不存在！或已下架。");
				}
				
				//商品余量是否>=num
				if(CacheClass.getIntValue(project.limitenum)<CacheClass.getIntValue(project.supportnum))
				{
					abortTransaction = true;
					throw new LzPluginException("商品库存不足！");
				}
				//计算总价
				totalrmb=totalrmb+num*CacheClass.getFloatValue(project.packageprice)+CacheClass.getFloatValue(project.frieght);
				
				alltotalrmb=alltotalrmb+totalrmb;
				goodsname=goodsname+project.projectname+";";
				//余额支付
				if (OnlinePayEnum.icard.getIntVlue() == paytype) //余额支付
				{
					//扣除余额
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("update cd_user_rmb set nowrmb=nowrmb-? where userid=? and nowrmb -?>=0");
					pstmt.setFloat(1,totalrmb);
					pstmt.setInt(2, userid);
					pstmt.setFloat(3,totalrmb);
					if(pstmt.executeUpdate()!=1)
					{
						abortTransaction = true;
						throw new LzPluginException("您的余额不足");
					}

					//取更新后的新值
					String nowrmb="0";
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("select nowrmb from cd_user_rmb where userid=?");
					pstmt.setInt(1, userid);
					rs=pstmt.executeQuery();
					if(rs.next())
					{
						nowrmb=rs.getString("nowrmb");
					}
					
					//增加积分使用记录
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("INSERT INTO cd_sys_rmb (userid,payuserid,rmb,level,type,time,name,image,nowrmb) VALUES (?,?,?,?,?,?,?,?,?)");
					pstmt.setInt(1, userid);
					pstmt.setInt(2, 0);
					pstmt.setFloat(3, 0-totalrmb);
					pstmt.setInt(4, SysRmbRecTypeEnum.type0.getIntVlue());
					pstmt.setInt(5, SysRmbRecTypeEnum.type0.getIntVlue());
					pstmt.setLong(6, now.getTime());
					pstmt.setString(7, project.projectname);
					pstmt.setString(8, project.projectimage);
					pstmt.setBigDecimal(9, CacheClass.toBigDecimal(nowrmb));
					pstmt.execute();	
						
				}
				//生成订单
				int OrderFinishDays=CacheClass.getIntValue(BaseDataCacheManager.getInstance().Load_Param("OrderFinishDays").trim());
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement(INSERT_SYS_ORDER);
				pstmt.setInt(1, userid);
				pstmt.setLong(2, CacheClass.getLongValue(user.mobile));
				pstmt.setInt(3, CacheClass.getIntValue(project.companyid));
				pstmt.setInt(4, projectid);
				pstmt.setString(5,project.projectname);

				if(CacheClass.isEmpty(selectextra))
					pstmt.setNull(6, Types.VARCHAR);
				else
					pstmt.setString(6, selectextra);			
					
				pstmt.setFloat(7, CacheClass.getFloatValue(project.packageprice));
				
				pstmt.setInt(8, CacheClass.getIntValue(project.viplevel));
				
				pstmt.setInt(9, CacheClass.getIntValue(project.days));
				
				pstmt.setInt(10, num);
				
				if(CacheClass.isEmpty(project.priceunits))
					pstmt.setNull(11, Types.VARCHAR);
				else
					pstmt.setString(11, project.priceunits);				
					
				pstmt.setFloat(12,  CacheClass.getFloatValue(project.frieght));
				pstmt.setFloat(13, totalrmb);
				pstmt.setInt(14, paytype);
				//余额付款 同样将付款状态统一放到后面更新处理
				pstmt.setInt(15, OrderStateEnum.to_pay.getIntVlue()); //未付款

				pstmt.setInt(16, 0);
				pstmt.setLong(17, now.getTime());
				pstmt.setString(18, address);
				
				if(CacheClass.getIntValue(user.iscompanyid)==0)
					pstmt.setNull(19, Types.VARCHAR);
				else
					pstmt.setInt(19, CacheClass.getIntValue(user.iscompanyid)); //smallcompanyid
				
				pstmt.setInt(20, CacheClass.getIntValue(user.istest));
				pstmt.setInt(21, alipayType);
				pstmt.setInt(22, OrderFinishDays);
				
				
				pstmt.execute();
			}
			
			//余额支付
			if (OnlinePayEnum.icard.getIntVlue() == paytype) //余额支付
			{
				//触发，系统消息发给消费人
				pushToAPPTemplate.NewMess_BuyGoodsRmb(userid, alltotalrmb);
			}
		} catch (Exception e) {
			tmp=null;
			abortTransaction = true;
			e.printStackTrace();
			if(e instanceof LzPluginException)
			{
				throw new LzPluginException(e.getMessage());
			}
			
		} finally {
			DbConnectionManager.closeTransactionConnection(rs,pstmt, con,abortTransaction);
		}
		
		if(!abortTransaction)//成功 更新相关缓存
		{
			if(goodsname.length()>30) goodsname=goodsname.substring(0,30);
			String orderid=load_OrderidListByUser(userid,now.getTime());
			String payid=Add_OrderidListByPayid(userid,orderid);
			String order_sn=payid+""+now.getTime();
			
			//取得刚才生成订单的id
			tmp=new OrderPayParm();
			tmp.totalrmb=alltotalrmb+"";
			tmp.payid=payid;
			tmp.userid=userid+"";
			tmp.projectname=goodsname;
			tmp.order_sn=order_sn;
			for(int i=0;i<strIdArr.length;i++)
			{
				CrowdSysProjectCache.getInstance().ClearObjectCache(strIdArr[i]);
			}
			CrowdUserAuthCache.getInstance().ClearObjectCache(userid+"");
		}

		return tmp;
	}
	
	//再支付之余额支付
	public CrowdOrderDataClass resultRepay_Order_ByUser(CrowdOrderDataClass tmp)	throws LzPluginException {

		float totalrmb = 0;
		//取用户数据
		CrowdUserAuthClass user=CrowdUserAuthCache.getInstance().getUser(CacheClass.getIntValue(tmp.userid));
		if(user==null )
		{
			throw new LzPluginException("用户不存在！");
		}

		//项目
		CrowdSysProjectClass project=CrowdSysProjectProvider.getInstance().load_Goods_Info_ById2(CacheClass.getIntValue(tmp.projectid));
		if(project==null)
		{
			throw new LzPluginException("商品不存在！或已下架。");
		}
		// 数据库操作
		boolean abortTransaction = false;
		Connection con = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Date now = new Date();
		try {
			
			//商品余量是否>=num
			if(CacheClass.getIntValue(project.limitenum)<CacheClass.getIntValue(project.supportnum))
			{
				abortTransaction = true;
				throw new LzPluginException("商品库存不足！");
			}
			//计算总价
			totalrmb=CacheClass.getFloatValue(tmp.totalrmb);
			con = DbConnectionManager.getTransactionConnection();
			//扣除余额
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_user_rmb set nowrmb=nowrmb-? where userid=? and nowrmb -?>=0");
			pstmt.setFloat(1,totalrmb);
			pstmt.setInt(2, CacheClass.getIntValue(tmp.userid));
			pstmt.setFloat(3,totalrmb);
			if(pstmt.executeUpdate()!=1)
			{
				abortTransaction = true;
				throw new LzPluginException("您的余额不足");
			}
			//取更新后的新值
			String nowrmb="0";
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select nowrmb from cd_user_rmb where userid=?");
			pstmt.setInt(1, CacheClass.getIntValue(tmp.userid));
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				nowrmb=rs.getString("nowrmb");
			}
			
			//增加积分使用记录
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("INSERT INTO cd_sys_rmb (userid,payuserid,rmb,level,type,time,name,image,nowrmb) VALUES (?,?,?,?,?,?,?,?,?)");
			pstmt.setInt(1, CacheClass.getIntValue(tmp.userid));
			pstmt.setInt(2, 0);
			pstmt.setFloat(3, 0-totalrmb);
			pstmt.setInt(4, SysRmbRecTypeEnum.type0.getIntVlue());
			pstmt.setInt(5, SysRmbRecTypeEnum.type0.getIntVlue());
			pstmt.setLong(6, now.getTime());
			pstmt.setString(7, project.projectname);
			pstmt.setString(8, project.projectimage);
			pstmt.setBigDecimal(9, CacheClass.toBigDecimal(nowrmb));
			pstmt.execute();
			
			
		} catch (Exception e) {
			tmp=null;
			abortTransaction = true;
			e.printStackTrace();
			if(e instanceof LzPluginException)
			{
				throw new LzPluginException(e.getMessage());
			}
			
		} finally {
			DbConnectionManager.closeTransactionConnection(rs,pstmt, con,abortTransaction);
		}
		
		if(!abortTransaction)//成功 更新相关缓存
		{
			CrowdUserAuthCache.getInstance().ClearObjectCache(tmp.userid);
		}

		return tmp;
	}
	
	//&新鲜宝   用户取订单详情记录
	public CrowdOrderDataClass loadOrderByID(int orderid){
		CrowdOrderDataClass tmp = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select o.*,c.companyname,pa.projectimage,pa.intro,c.address as compaddress,c.tel,c.qq from cd_sys_order o " +
					" left join cd_sys_goods pa on pa.projectid=o.projectid " +
					" left join cd_sys_company c on c.companyid=o.companyid " +
					" where o.orderid = ?");
			pstmt.setInt(1, orderid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				tmp=encapOrderData(rs);
			}
		} catch (Exception e) {
			tmp=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return tmp;
	}	
	
	
		
	
	//&新鲜宝  用户取订单详情记录
	public CrowdOrderDataClass load_OrderByUser_id(int userid,long ctime) {
		CrowdOrderDataClass tmp = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select o.*,c.companyname,pa.projectimage,pa.intro,c.address as compaddress,c.tel,c.qq from cd_sys_order o " +
					" left join cd_sys_goods pa on pa.projectid=o.projectid " +
					" left join cd_sys_company c on c.companyid=o.companyid " +
					" where o.userid=? and o.ctime=?");
			pstmt.setInt(1, userid);
			pstmt.setLong(2, ctime);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				tmp=encapOrderData(rs);
			}
		} catch (Exception e) {
			tmp=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return tmp;

	}
	
	
	//&新鲜宝  用户取订单详情记录
	public String load_OrderidListByUser(int userid,long ctime) {
		String orderid = "";
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select GROUP_CONCAT(orderid) as orderidlist from cd_sys_order where userid=? and ctime=?");
			pstmt.setInt(1, userid);
			pstmt.setLong(2, ctime);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				orderid=rs.getString("orderidlist");
			}
		} catch (Exception e) {
			orderid = "";
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return orderid;

	}
	
	
	public String load_OrderidListByPayid(int payid ,boolean iserror) {
		String orderidlist = "";
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select orderidlist from cd_sys_payid where payid=?");
			pstmt.setInt(1, payid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				orderidlist=rs.getString("orderidlist");
			}
			
			//支付成功
			if(!iserror)
			{
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("update cd_sys_payid set state=1 where payid=?");
				pstmt.setInt(1, payid);
				pstmt.executeUpdate();
			}
		} catch (Exception e) {
			orderidlist = "";
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return orderidlist;

	}
	
	public String Add_OrderidListByPayid(int userid,String orderidlist) {
		String payid = "";
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Date now = new Date();
		try {
			
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("insert into cd_sys_payid (orderidlist,userid,state,ctime) values (?,?,?,?) ");
			pstmt.setString(1, orderidlist);
			pstmt.setInt(2, userid);
			pstmt.setInt(3, 0);
			pstmt.setLong(4, now.getTime());
			pstmt.execute();
			//查得刚插入的记录id
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select payid from cd_sys_payid where userid=? and ctime=? ");
			pstmt.setInt(1, userid);
			pstmt.setLong(2, now.getTime());
			rs=pstmt.executeQuery();
			if (rs.next()) {
				payid=rs.getString("payid");
			}
		} catch (Exception e) {
			payid = "";
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return payid;

	}	
	
		
	//&新鲜宝   用户查询订单列表
	public PublicData load_OrderByUser_id(int userid,int type, int curpage) {
		
		PublicData data = new PublicData();
		ArrayList<CrowdOrderDataClass> list = new ArrayList<CrowdOrderDataClass>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "{call getUserOrder(?,?,?)}";
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareCall(sql);
			pstmt.setInt(1, userid);
			pstmt.setInt(2, type);
			pstmt.setInt(3, curpage);
			pstmt.execute();

			rs = pstmt.getResultSet();
			while (rs.next()) {
				list.add(encapOrderData(rs));
			}

			pstmt.getMoreResults();
			rs = pstmt.getResultSet();
			int total = 0;
			while (rs != null && rs.next()) {
				total = rs.getInt(1);
			}
			ArrayList<Object> returnlist = new ArrayList<Object>();
			if ((total % 20) > 0) {
				total = (total / 20 + 1);
			} else
				total = (total / 20);

			if (total == 0)
				total = 1;
			data.total = total + "";
			data.curpage = curpage + "";
			returnlist.addAll(list);
			data.list = returnlist;
			data.state = "1";
		} catch (Exception e) {
			list = null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return data;
	}
	
	

	//&新鲜宝  可赎回订单
	public List<CrowdOrderDataClass> load_Owe2OrderByUser_id(int userid) {
		
		List<CrowdOrderDataClass> list = new ArrayList<CrowdOrderDataClass>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "{call getUserOwe2Order(?)}";
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareCall(sql);
			pstmt.setInt(1, userid);
			pstmt.execute();

			rs = pstmt.getResultSet();
			while (rs.next()) {
				list.add(encapOrderData(rs));
			}
		} catch (Exception e) {
			list = null;
			e.printStackTrace();
			
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}

		return list;
	}	
	
	

	
	//&新鲜宝 确认收货
	public CrowdOrderDataClass Update_sign(int order_id, int user_id) throws LzPluginException {
		Connection con = null;
		PreparedStatement pstmt = null;
		CrowdOrderDataClass tmp = CrowdOrderCache.getInstance().loadOrderByID(order_id);
		if (tmp == null) {
			throw new LzPluginException("找不到订单" + order_id);
		}
	

		
		Date now = new Date();
		long time= now.getTime();
		boolean abortTransaction = false;
		try {
			con = DbConnectionManager.getTransactionConnection();
			// 修改订单
			pstmt = con.prepareStatement(UPDATE_SYS_ORDER_USER_CONFIRM);
			pstmt.setInt(1, OrderStateEnum.arrive.getIntVlue());
			pstmt.setLong(2,time);
			pstmt.setInt(3, order_id);
			pstmt.setInt(4, user_id);
			pstmt.executeUpdate();

			tmp.state = OrderStateEnum.arrive.getIntVlue() + "";
			tmp.arrivetime = time + "";
			tmp.stateAbout =OrderStateEnum.getEnumString(CacheClass.getIntValue(tmp.state));
		} catch (Exception e) {
			tmp = null;
			abortTransaction = true;
			if(e instanceof LzPluginException)
			{
				throw new LzPluginException(e.getMessage());
			}
		} finally {
			DbConnectionManager.closeTransactionConnection(pstmt, con,abortTransaction);
		}
		//成功 更新相关缓存
		if (!abortTransaction) {

			return tmp;
			
		} else {
			return null;
		}
		
	}

		
	//&新鲜宝   用户取消订单
	public CrowdOrderDataClass cancelOrder(int orderid,int userid) throws LzPluginException {
		Connection con = null;
		PreparedStatement pstmt = null;
		CrowdOrderDataClass tmp = CrowdOrderCache.getInstance().loadOrderByID(orderid);
		if (tmp == null) {
			throw new LzPluginException("找不到订单" + orderid);
		}
		Date now = new Date();
		long time= now.getTime();
		boolean abortTransaction = false;
		try {
			con = DbConnectionManager.getTransactionConnection();
			// 修改订单
			pstmt = con.prepareStatement(UPDATE_SYS_ORDER_USER_CONFIRM2);
			pstmt.setInt(1, OrderStateEnum.cancel.getIntVlue());
			pstmt.setLong(2,time);
			pstmt.setInt(3, orderid);
			pstmt.setInt(4, userid);
			if(pstmt.executeUpdate()==1)
			{
				tmp.state = OrderStateEnum.cancel.getIntVlue() + "";
				tmp.canceltime = time + "";
				//用户取消订单 时，只有待付款情况下才可取消订单，所以这里不存在退款
			}
			
		
		} catch (Exception e) {
			tmp = null;
			abortTransaction = true;
			if(e instanceof LzPluginException)
			{
				throw new LzPluginException(e.getMessage());
			}
		} finally {
			DbConnectionManager.closeTransactionConnection(pstmt, con,abortTransaction);
		}
		//成功 更新相关缓存
		if (!abortTransaction) {
			return tmp;
		} else {
			return null;
		}

	}
	
	
	
	
	//&新鲜宝   再次支付　更新订单支付方式
	public CrowdOrderDataClass updateOrderForPayByID(int orderid,int paytype,int alipayType) throws LzPluginException {
		Connection con = null;
		PreparedStatement pstmt = null;
		CrowdOrderDataClass tmp = CrowdOrderCache.getInstance().loadOrderByID(orderid);
		if (tmp == null) {
			throw new LzPluginException("找不到订单" + orderid);
		}
		try {
			con = DbConnectionManager.getConnection();
			// 修改订单
			pstmt = con.prepareStatement("UPDATE cd_sys_order SET paytype=?,alipaytype=? where orderid=? ");
			pstmt.setInt(1, paytype);
			pstmt.setInt(2, alipayType);
			pstmt.setInt(3, orderid);
			if(pstmt.executeUpdate()==1)
			{
				tmp.paytype = paytype + "";
			}

			String orderId=tmp.orderid;
			String payid=Add_OrderidListByPayid(CacheClass.getIntValue(tmp.userid),orderId);
			tmp.payid=payid;
		
		} catch (Exception e) {
			tmp = null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}

		return tmp;


	}
	
	
	//&新鲜宝   再次支付　复制为一个新的订单
	public CrowdOrderDataClass OrderForPayByID_insert_delete(int orderid,int paytype,int alipayType) throws LzPluginException {
		Connection con = null;
		PreparedStatement pstmt = null;
		CrowdOrderDataClass tmp = CrowdOrderCache.getInstance().loadOrderByID(orderid);
		CrowdOrderDataClass obj=null;
		ResultSet rs = null;
		if (tmp == null) {
			throw new LzPluginException("找不到订单" + orderid);
		}
		try {
			//复制为一个新的订单
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(INSERT_SYS_ORDER);
			pstmt.setInt(1, CacheClass.getIntValue(tmp.userid));
			pstmt.setLong(2, CacheClass.getLongValue(tmp.mobile));
			pstmt.setInt(3, CacheClass.getIntValue(tmp.companyid));
			pstmt.setInt(4, CacheClass.getIntValue(tmp.projectid));
			pstmt.setString(5,tmp.projectname);
			pstmt.setString(6, tmp.selectextra);			
			pstmt.setFloat(7, CacheClass.getFloatValue(tmp.packageprice));
			pstmt.setInt(8, CacheClass.getIntValue(tmp.days));
			pstmt.setInt(9, CacheClass.getIntValue(tmp.ordernum));
			pstmt.setString(10, tmp.priceunits);				
			pstmt.setFloat(11,  CacheClass.getFloatValue(tmp.frieght));
			pstmt.setFloat(12, CacheClass.getFloatValue(tmp.totalrmb));
			pstmt.setInt(13, paytype);
			pstmt.setInt(14, OrderStateEnum.to_pay.getIntVlue()); //未付款
			pstmt.setInt(15, 0);
			pstmt.setLong(16, CacheClass.getLongValue(tmp.ctime));
			pstmt.setString(17, tmp.address);
			pstmt.setInt(18, CacheClass.getIntValue(tmp.smallcompanyid)); 
			pstmt.setInt(19, CacheClass.getIntValue(tmp.istest));
			if(paytype!=OnlinePayEnum.appliy.getIntVlue())
			{
				alipayType=0;
			}
			pstmt.setInt(20, alipayType);
			pstmt.execute();
			//取到最新的
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select o.*,c.companyname,pa.projectimage,pa.intro,c.address as compaddress,c.tel,c.qq from cd_sys_order o " +
					" left join cd_sys_goods pa on pa.projectid=o.projectid " +
					" left join cd_sys_company c on c.companyid=o.companyid " +
					" where o.userid=? and o.ctime=? order by orderid desc LIMIT 1");
			pstmt.setInt(1, CacheClass.getIntValue(tmp.userid));
			pstmt.setLong(2, CacheClass.getLongValue(tmp.ctime));
			rs = pstmt.executeQuery();
			if (rs.next()) {
				obj=encapOrderData(rs);
				
				String orderId=obj.orderid;
				String payid=Add_OrderidListByPayid(CacheClass.getIntValue(obj.userid),orderId);
				obj.payid=payid;
			}
		
			
			//删除原来的
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("delete from cd_sys_order where orderid=?");
			pstmt.setLong(1, CacheClass.getLongValue(tmp.orderid));
			pstmt.execute();
			
			CrowdSysProjectCache.getInstance().ClearObjectCache(tmp.orderid);
		
		} catch (Exception e) {
			obj = null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}

		return obj;


	}
	
	
	
	
	
	//&新鲜宝    订单第三方支付   结果更新数据库等 
	public boolean Deal_Order(String orderId,int paytype, boolean iserror,	String errormsg) throws LzPluginException   {
		
		boolean abortTransaction = false;
		
		int orderid=CacheClass.getIntValue(orderId);
		CrowdOrderDataClass tmp = loadOrderByID(orderid);
		if (tmp == null) {
			throw new LzPluginException("找不到订单" + orderid);
		}
		if (CacheClass.getIntValue(tmp.state) > OrderStateEnum.to_pay.getIntVlue()) {
			return false;
		}
		if (CacheClass.getIntValue(tmp.starttime) > 0) {
			return false;
		}
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs =null;
		
		int userid = CacheClass.getIntValue(tmp.userid);
		int projectid=CacheClass.getIntValue(tmp.projectid);
		int num=CacheClass.getIntValue(tmp.ordernum);
		float rmb=CacheClass.getFloatValue(tmp.totalrmb);
		int istest=CacheClass.getIntValue(tmp.istest);
		int vrgoodsid=0;
		VrGoodsDataClass vrgoods=VrGoodsCache.getInstance().Load_VrGoods_ByListFirst();
		if(vrgoods!=null)
		{
			vrgoodsid=CacheClass.getIntValue(vrgoods.vrgoodsid);
		}
		
		
		try {
			// 组成SQL语名
			if (iserror) {
				con = DbConnectionManager.getTransactionConnection();
				pstmt = con.prepareStatement("update cd_sys_order set errormsg=? where orderid=?");
				pstmt.setString(1, errormsg);
				pstmt.setInt(2, orderid);
				pstmt.executeUpdate();
				
			} else {
				Date now = new Date();
				long time= now.getTime();
				// 组成SQL语名
				con = DbConnectionManager.getTransactionConnection();
				//众筹项目支持量修改
				
				if(istest!=1)
				{
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("update cd_sys_goods set supportnum=(supportnum+?),nowrmb=(nowrmb+?) where projectid=?");
					pstmt.setInt(1, num);
					pstmt.setFloat(2, rmb);
					pstmt.setInt(3, projectid);
					pstmt.executeUpdate();			
				}
				//修改订单状态
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("update cd_sys_order set state=?,starttime=?,paytype=? where orderid=?");
				pstmt.setInt(1, OrderStateEnum.prepare.getIntVlue());
				pstmt.setLong(2, time);
				pstmt.setInt(3, paytype);
				pstmt.setInt(4, orderid);
				pstmt.executeUpdate();
				
				//购买人经验值增加
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("update cd_user_rmb set totalbuyscore=(totalbuyscore+"+rmb+") where userid="+userid);
				pstmt.executeUpdate();
				
				//购买人经验值增加 及 增加送的现货瓶数
				if(vrgoodsid>0)
				{
					//取当前现货价格
					float vrgoodsprice=CacheClass.getFloatValue(vrgoods.price);
					//取送现货价值比率
					float vrgoodsrate=CacheClass.getFloatValue(CacheClass.getSYS_BaseDataCacheManager().Load_Param("vrgoodsrate").trim());
					//送现货手瓶数
					float vrgoodsnum=rmb*vrgoodsrate/(vrgoodsprice*100f);
			
					//现货品种瓶数更新
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("select * from cd_user_num where userid=? and vrgoodsid=? ;");
					pstmt.setInt(1, userid);
					pstmt.setInt(2, vrgoodsid);
					rs=pstmt.executeQuery();
					if(rs.next()) //存在 则更新
					{
						DbConnectionManager.fastcloseStmt(rs,pstmt);
						pstmt = con.prepareStatement("update cd_user_num set num=(num+?) where userid=? and vrgoodsid=? ;");
						pstmt.setBigDecimal(1, CacheClass.toBigDecimal(vrgoodsnum+""));
						pstmt.setInt(2, userid);
						pstmt.setInt(3, vrgoodsid);
						pstmt.executeUpdate();
						
					}
					else//不存在  则插入
					{
						DbConnectionManager.fastcloseStmt(rs,pstmt);
						pstmt = con.prepareStatement("insert into cd_user_num (userid,vrgoodsid,num) VALUES (?,?,?);");
						pstmt.setInt(1, userid);
						pstmt.setInt(2, vrgoodsid);
						pstmt.setBigDecimal(3, CacheClass.toBigDecimal(vrgoodsnum+""));
						pstmt.execute();
						
					}
					
					
					//取更新后的新值
					String manowtotalrmb="0";
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("select num from cd_user_num where userid=? and vrgoodsid=? ;");
					pstmt.setInt(1, userid);
					pstmt.setInt(2, vrgoodsid);
					rs=pstmt.executeQuery();
					if(rs.next())
					{
						manowtotalrmb=rs.getString("num");
					}
					
					//增加现货瓶数变动记录
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("INSERT INTO cd_sys_rmb (userid,payuserid,rmb,level,type,time,name,image,nowrmb,orderid,vrgoodsid) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
					pstmt.setInt(1, userid);
					pstmt.setInt(2, 0);
					pstmt.setBigDecimal(3, CacheClass.toBigDecimal(vrgoodsnum+""));
					pstmt.setInt(4, SysRmbRecTypeEnum.type100.getIntVlue());
					pstmt.setInt(5, SysRmbRecTypeEnum.type100.getIntVlue());
					pstmt.setLong(6, now.getTime());
					pstmt.setString(7, tmp.projectname);
					pstmt.setString(8, tmp.projectimage);
					pstmt.setBigDecimal(9, CacheClass.toBigDecimal(manowtotalrmb));
					pstmt.setInt(10, orderid);
					pstmt.setInt(11, vrgoodsid);
					
					pstmt.execute();
				}
				
					
				
				CrowdUserAuthClass user=CrowdUserAuthCache.getInstance().getUser(userid);
				//当前用户的vip等级与剩余天数
				int curViplevel=CacheClass.getIntValue(user.viplevel);
				int day=0;
				if(curViplevel>0 && CacheClass.getIntValue(user.vipdate)!=0)
				{
					Date starDate=CacheClass.strToDate(user.vipdate, "yyyyMMdd");
					day=CacheClass.GetDateSubDay(starDate,now);
				}
				if(day<0 || curViplevel==0) 
				{
					day=0;
					curViplevel=0;
				}
				int toViplevel=curViplevel;
				
				//增加vip期
				if(CacheClass.getIntValue(tmp.viplevel)!=0 && CacheClass.getIntValue(tmp.days)>0)
				{
					//订单的
					int orderViplevel=CacheClass.getIntValue(tmp.viplevel);
					int orderDay=CacheClass.getIntValue(tmp.days);
					//处理
					if(orderViplevel>curViplevel)
					{
						toViplevel=orderViplevel;
					}
					int tmpcurDay=day*curViplevel; //折算到1级的天数
					int tmporderDay=orderDay*orderViplevel; //折算到1级的天数
					//得到新等级与新的天数，从而得到新的有效截止期
					int toDay=(num*tmporderDay+tmpcurDay)/toViplevel;
					int toVipdate=CacheClass.getDateAfterDay2(new Date(), toDay);
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("update cd_user_auth set viplevel=?,vipdate=? where userid=?");
					pstmt.setInt(1, toViplevel);
					pstmt.setInt(2, toVipdate);
					pstmt.setInt(2, userid);
					pstmt.executeUpdate();
				
				}
				
				if(istest!=1)
				{
					//购买者及上三级分成
					BuyCutThread buyCut=new BuyCutThread( rmb,num,userid,curViplevel,tmp.orderid,tmp.projectid,tmp.projectname);
					buyCut.start();
					
					//购物活跃值增加
					InviteCutThread  prestige=new InviteCutThread(userid,1,rmb,CacheClass.getIntValue(tmp.orderid));
					prestige.start();
				}
			}

		} catch (Exception e) {
			abortTransaction = true;
			e.printStackTrace();
			if(e instanceof LzPluginException)
			{
				throw new LzPluginException(e.getMessage());
			}
		} finally {
			DbConnectionManager.closeTransactionConnection(rs,pstmt, con,abortTransaction);
		}

		//执行成功 更新相关缓存
		if(!abortTransaction)
		{
			if (!iserror)
			{
				CrowdUserAuthCache.getInstance().ClearObjectCache(userid+"");
				CrowdOrderCache.getInstance().ClearObjectCache(orderid+"");
				CrowdSysProjectCache.getInstance().ClearObjectCache(projectid+"");
				
				CrowdUserAuthClass auth=CrowdUserAuthCache.getInstance().getUser(userid);
				if(CacheClass.getIntValue(auth.isvruser)==0)
				{
					//理财帐户开启
					CrowdUserAuthCache.getInstance().UPDATA_User_auth_Isvruser(userid);			
				}
			}
			

		}
		
		return abortTransaction;

	}

	
	//&新鲜宝  订单支付结果更新数据库等  余额支付  
	public boolean Deal_Order(String orderId) throws LzPluginException {

		boolean abortTransaction = false;
		
		int orderid=CacheClass.getIntValue(orderId);
		CrowdOrderDataClass tmp = loadOrderByID(orderid);
		if (tmp == null) {
			throw new LzPluginException("找不到订单" + orderid);
		}
		if (CacheClass.getIntValue(tmp.state) > OrderStateEnum.to_pay.getIntVlue()) {
			return true;
		}
		if (CacheClass.getIntValue(tmp.starttime) > 0) {
			return true;
		}
		
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs =null;
		
		int userid = CacheClass.getIntValue(tmp.userid);
		int projectid=CacheClass.getIntValue(tmp.projectid);
		int num=CacheClass.getIntValue(tmp.ordernum);
		float rmb=CacheClass.getFloatValue(tmp.totalrmb);
		int istest=CacheClass.getIntValue(tmp.istest);
		
		int vrgoodsid=0;
		VrGoodsDataClass vrgoods=VrGoodsCache.getInstance().Load_VrGoods_ByListFirst();
		if(vrgoods!=null)
		{
			vrgoodsid=CacheClass.getIntValue(vrgoods.vrgoodsid);
		}
		
		CrowdUserAuthClass user=CrowdUserAuthCache.getInstance().getUser(userid);
		

		 
		try {
			Date now = new Date();
			long time= now.getTime();
			// 组成SQL语名
			con = DbConnectionManager.getTransactionConnection();
			//众筹项目支持量修改
			if(istest!=1)
			{
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("update cd_sys_goods set supportnum=(supportnum+?),nowrmb=(nowrmb+?) where projectid=?");
				pstmt.setInt(1, num);
				pstmt.setFloat(2, rmb);
				pstmt.setInt(3, projectid);
				pstmt.executeUpdate();
			}
			
			//修改订单状态
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_sys_order set state=?,starttime=? where orderid=?");
			pstmt.setInt(1, OrderStateEnum.prepare.getIntVlue());
			pstmt.setLong(2, time);
			pstmt.setInt(3, orderid);
			pstmt.executeUpdate();
			//购买人经验值增加
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_user_rmb set totalbuyscore=(totalbuyscore+"+rmb+") where userid="+userid);
			pstmt.executeUpdate();
			
			//购买人经验值增加 及 增加送的现货瓶数
			if(vrgoodsid>0)
			{
				//取当前现货价格
				float vrgoodsprice=CacheClass.getFloatValue(vrgoods.price);
				//取送现货价值比率
				float vrgoodsrate=CacheClass.getFloatValue(CacheClass.getSYS_BaseDataCacheManager().Load_Param("vrgoodsrate").trim());
				//送现货手瓶数(总数更新)
				float vrgoodsnum=rmb*vrgoodsrate/(vrgoodsprice*100f);
				
				//现货品种瓶数更新
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("select * from cd_user_num where userid=? and vrgoodsid=? ;");
				pstmt.setInt(1, userid);
				pstmt.setInt(2, vrgoodsid);
				rs=pstmt.executeQuery();
				if(rs.next()) //存在 则更新
				{
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("update cd_user_num set num=(num+?) where userid=? and vrgoodsid=? ;");
					pstmt.setBigDecimal(1, CacheClass.toBigDecimal(vrgoodsnum+""));
					pstmt.setInt(2, userid);
					pstmt.setInt(3, vrgoodsid);
					pstmt.executeUpdate();
					
				}
				else//不存在  则插入
				{
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("insert into cd_user_num (userid,vrgoodsid,num) VALUES (?,?,?);");
					pstmt.setInt(1, userid);
					pstmt.setInt(2, vrgoodsid);
					pstmt.setBigDecimal(3, CacheClass.toBigDecimal(vrgoodsnum+""));
					pstmt.execute();
					
				}
				
				//取更新后的新值
				String manowtotalrmb="0";
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("select num from cd_user_num where userid=? and vrgoodsid=? ;");
				pstmt.setInt(1, userid);
				pstmt.setInt(2, vrgoodsid);
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					manowtotalrmb=rs.getString("num");
				}
				
				//增加现货瓶数变动记录
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("INSERT INTO cd_sys_rmb (userid,payuserid,rmb,level,type,time,name,image,nowrmb,orderid,vrgoodsid) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
				pstmt.setInt(1, userid);
				pstmt.setInt(2, 0);
				pstmt.setBigDecimal(3, CacheClass.toBigDecimal(vrgoodsnum+""));
				pstmt.setInt(4, SysRmbRecTypeEnum.type100.getIntVlue());
				pstmt.setInt(5, SysRmbRecTypeEnum.type100.getIntVlue());
				pstmt.setLong(6, now.getTime());
				pstmt.setString(7, tmp.projectname);
				pstmt.setString(8, tmp.projectimage);
				pstmt.setBigDecimal(9, CacheClass.toBigDecimal(manowtotalrmb));
				pstmt.setInt(10, orderid);
				pstmt.setInt(11, vrgoodsid);
				pstmt.execute();
				
			}
			
			user=CrowdUserAuthCache.getInstance().getUser(userid);
			//当前用户的vip等级与剩余天数
			int curViplevel=CacheClass.getIntValue(user.viplevel);
			int day=0;
			if(curViplevel>0 && CacheClass.getIntValue(user.vipdate)!=0)
			{
				Date starDate=CacheClass.strToDate(user.vipdate, "yyyyMMdd");
				day=CacheClass.GetDateSubDay(starDate,now);
			}
			if(day<0 || curViplevel==0) 
			{
				day=0;
				curViplevel=0;
			}
			int toViplevel=curViplevel;
			//增加vip期
			if(CacheClass.getIntValue(tmp.viplevel)!=0 && CacheClass.getIntValue(tmp.days)>0)
			{
				//订单的
				int orderViplevel=CacheClass.getIntValue(tmp.viplevel);
				int orderDay=CacheClass.getIntValue(tmp.days);
				//处理
				if(orderViplevel>curViplevel)
				{
					toViplevel=orderViplevel;
				}
				int tmpcurDay=day*curViplevel; //折算到1级的天数
				int tmporderDay=orderDay*orderViplevel; //折算到1级的天数
				//得到新等级与新的天数，从而得到新的有效截止期
				int toDay=(num*tmporderDay+tmpcurDay)/toViplevel;
				int toVipdate=CacheClass.getDateAfterDay2(new Date(), toDay);
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("update cd_user_auth set viplevel=?,vipdate=? where userid=?");
				pstmt.setInt(1, toViplevel);
				pstmt.setInt(2, toVipdate);
				pstmt.setInt(3, userid);
				pstmt.executeUpdate();
			}

			if(istest!=1)
			{
				//购买者及上三级分成
				BuyCutThread buyCut=new BuyCutThread( rmb,num,userid,curViplevel,tmp.orderid,tmp.projectid,tmp.projectname);
				buyCut.start();
				
				//购物活跃值增加
				InviteCutThread  prestige=new InviteCutThread(userid,1,rmb,CacheClass.getIntValue(tmp.orderid));
				prestige.start();
			}
			
		} catch (Exception e) {
			abortTransaction = true;
			userid=0;
			e.printStackTrace();
			if(e instanceof LzPluginException)
			{
				throw new LzPluginException(e.getMessage());
			}
			
		} finally {
			DbConnectionManager.closeTransactionConnection(rs,pstmt, con,abortTransaction);
		}

		//执行成功 更新相关缓存
		if(!abortTransaction)
		{
			CrowdUserAuthCache.getInstance().ClearObjectCache(userid+"");
			CrowdOrderCache.getInstance().ClearObjectCache(orderid+"");
			CrowdSysProjectCache.getInstance().ClearObjectCache(projectid+"");
			
			CrowdUserAuthClass auth=CrowdUserAuthCache.getInstance().getUser(userid);
			if(CacheClass.getIntValue(auth.isvruser)==0)
			{
				//理财帐户开启
				CrowdUserAuthCache.getInstance().UPDATA_User_auth_Isvruser(userid);			
			}
		}
		
		return abortTransaction;

	}
	
	
	
	//&新鲜宝   取准备抵押的订单 以做检验用
	public List<CrowdOrderDataClass> loadOrderByordeidlist(int userid,String orderidlist){
		List<CrowdOrderDataClass> list = new ArrayList<CrowdOrderDataClass>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select o.*,c.companyname,pa.projectimage,pa.intro,c.address as compaddress,c.tel,c.qq from cd_sys_order o " +
					" left join cd_sys_goods pa on pa.projectid=o.projectid " +
					" left join cd_sys_company c on c.companyid=o.companyid " +
					" where o.orderid in ("+orderidlist+") and o.userid=? and o.state in (2,3) and o.owestate=0 and o.oweid is not null ");
			pstmt.setInt(1, userid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(encapOrderData(rs));
			}
		} catch (Exception e) {
			list=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return list;
	}	

	
	//&新鲜宝   取准备赎回的订单 以做检验用
	public List<CrowdOrderDataClass> loadOrderByordeidlist2(int userid,String orderidlist){
		List<CrowdOrderDataClass> list = new ArrayList<CrowdOrderDataClass>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select o.*,c.companyname,pa.projectimage,pa.intro,c.address as compaddress,c.tel,c.qq from cd_sys_order o " +
					" left join cd_sys_goods pa on pa.projectid=o.projectid " +
					" left join cd_sys_company c on c.companyid=o.companyid " +
					" where o.orderid in ("+orderidlist+") and o.userid=? and o.state>1 and o.owestate=1 ");
			pstmt.setInt(1, userid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(encapOrderData(rs));
			}
		} catch (Exception e) {
			list=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return list;
	}	
	
	
	
	//&新鲜宝   取准备申请发货的订单 以做检验用
	public List<CrowdOrderDataClass> loadOrderByorderidlistForRefunds(int userid,String orderidlist){
		List<CrowdOrderDataClass> list = new ArrayList<CrowdOrderDataClass>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select o.*,c.companyname,pa.projectimage,pa.intro,c.address as compaddress,c.tel,c.qq from cd_sys_order o " +
					" left join cd_sys_goods pa on pa.projectid=o.projectid " +
					" left join cd_sys_company c on c.companyid=o.companyid " +
					" where o.orderid in ("+orderidlist+") and o.userid=? and o.state=3 and o.owestate=0 ");
			pstmt.setInt(1, userid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(encapOrderData(rs));
			}
		} catch (Exception e) {
			list=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return list;
	}	
	
	/** 定时任务  0点0分定时执行，每隔24小时执行一次   主要完成 对众筹订单的   状态修改，如：可以早请发货否  是否抵押到期了 等**/
	public void AutoCheckCdOrder(Date startDate)
	{
	
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			long t=startDate.getTime();
			//对待取消订单超过7*24小时的订单删除 
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("DELETE from cd_sys_order where state=0 and starttime is null and ctime<?;");
			pstmt.setLong(1, t-7*24*3600*1000l);
			pstmt.execute();

			
			//对payid表超过60天*24小时的记录删除 
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("DELETE from cd_sys_payid where state=0 and ctime<?;");
			pstmt.setLong(1, t-60*24*3600*1000l);
			pstmt.execute();
			
			//统计15天前用户的瓶数= 当前的瓶数   - 近15天的瓶数变动之和
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_user_num set num15days=0;");
			pstmt.executeUpdate();
			
			int inFoundvailDay=CacheClass.getIntValue(CacheClass.getSYS_BaseDataCacheManager().Load_Param("inFoundvailDay").trim());
			Date last15Date=CacheClass.getDateBeforeDay(startDate, inFoundvailDay);
			
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_user_num n,(select userid,vrgoodsid,sum(rmb) as num15days  from cd_sys_rmb where (type BETWEEN 100 and 199) and time>? GROUP BY userid,vrgoodsid) tb "+ 
										" set n.num15days=(n.num-tb.num15days) where n.userid=tb.userid and n.vrgoodsid=tb.vrgoodsid;");
			pstmt.setLong(1, last15Date.getTime());
			pstmt.executeUpdate();
			
			//对宝箱过期处理 
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_user_redbag set state=2 where state=0 and endtime<?;");
			pstmt.setInt(1, CacheClass.getDateBeforeDay2_Int(new Date(), 0));
			pstmt.executeUpdate();
			
			//新增日寄售成交均价
			
			List<analy_vrgoodsprice> tmplist=new ArrayList<analy_vrgoodsprice>();
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select vrgoodsid from cd_sys_usershoplog group by vrgoodsid ;");
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				analy_vrgoodsprice tmp=new analy_vrgoodsprice();
				tmp.vrgoodsid=rs.getInt("vrgoodsid");
				tmplist.add(tmp);
			}
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select vrgoodsid,sum(num) totalnum,sum(rmb) totalrmb from cd_sys_usershoplog where time=? group by vrgoodsid ;");
			pstmt.setInt(1, CacheClass.getDateBeforeDay2_Int(startDate, 1));
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				int vrgoodsid=rs.getInt("vrgoodsid");
				for(analy_vrgoodsprice tmp:tmplist)
				{
					if(tmp.vrgoodsid==vrgoodsid)
					{
						tmp.price=CacheClass.Bdiv(rs.getString("totalrmb"), rs.getString("totalnum"), 2) ;
					}
				}
			}
			
			
			for(analy_vrgoodsprice tmp:tmplist)
			{
				if(CacheClass.getFloatValue(tmp.price)==0) //当天没有成交
				{
					//取上一交易日的日成交均价
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("select price from cd_analy_ymdprice where vrgoodsid=? order by ymdid desc limit 1;");
					pstmt.setInt(1, tmp.vrgoodsid);
					rs=pstmt.executeQuery();
					if(rs.next())
					{
						tmp.price=rs.getString("price");
					}
					if(CacheClass.getFloatValue(tmp.price)==0)//此前没有成交过，则取默认价
					{
						VrGoodsDataClass obj=VrGoodsCache.getInstance().Load_VrGoods_ByGoodsid(tmp.vrgoodsid+"");
						tmp.price=obj.price;
					}
				}
				
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("insert into cd_analy_ymdprice (ymdid,vrgoodsid,price) values (?,?,?);");
				pstmt.setInt(1, CacheClass.getDateBeforeDay2_Int(startDate, 1));
				pstmt.setInt(2, tmp.vrgoodsid);
				pstmt.setBigDecimal(3, CacheClass.getBigDecimal(tmp.price));
				pstmt.execute();
			}
			
			//对寄售上架处理 
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_user_shop set state=1 where state=0 and stime<=?;");
			pstmt.setInt(1, CacheClass.getDateBeforeDay2_Int(new Date(), 0));
			pstmt.executeUpdate();
			
			//对寄售自动撒单处理 
			List<String> sellidList = new ArrayList<String>();
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select sellid from cd_user_shop where state=1 and endtime<=?;");
			pstmt.setInt(1, CacheClass.getDateBeforeDay2_Int(new Date(), 0));
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				sellidList.add(rs.getString("sellid"));
			}
			//自动撒单
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_user_shop set state=3 where state=1 and endtime<=?;");
			pstmt.setInt(1, CacheClass.getDateBeforeDay2_Int(new Date(), 0));
			pstmt.executeUpdate();
			//退回瓶数
			for(String sellid:sellidList)
			{
				int userid=0;
				float num=0;
				int type=0;
				float numed=0;
				int vrgoodsid=0;
				String sellprice="0";
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("select userid,vrgoodsid,num,price,numed,type from cd_user_shop where sellid=?;");
				pstmt.setInt(1, CacheClass.getIntValue(sellid));
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					userid=rs.getInt("userid");
					num=rs.getFloat("num");
					type=rs.getInt("type");
					numed=rs.getFloat("numed");
					sellprice=rs.getString("price");
					vrgoodsid=rs.getInt("vrgoodsid");
				}
				
				if(type==0 && userid>0 && num>0 && vrgoodsid>0) //该寄售为出售,退回瓶数
				{
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("update cd_user_num set num=(num+?) where userid=? and vrgoodsid=? ;");
					pstmt.setBigDecimal(1, CacheClass.toBigDecimal(num+""));
					pstmt.setInt(2, userid);
					pstmt.setInt(3, vrgoodsid);
					pstmt.executeUpdate();
					
					//取更新后的新值
					String manowtotalrmb="0";
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("select num from cd_user_num where userid=? and vrgoodsid=? ;");
					pstmt.setInt(1, userid);
					pstmt.setInt(2, vrgoodsid);
					rs=pstmt.executeQuery();
					if(rs.next())
					{
						manowtotalrmb=rs.getString("num");
					}
					//增加现货瓶数变动记录
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("INSERT INTO cd_sys_rmb (userid,payuserid,rmb,level,type,time,name,image,nowrmb,orderid,vrgoodsid) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
					pstmt.setInt(1, userid);
					pstmt.setInt(2, 0);
					pstmt.setBigDecimal(3, CacheClass.toBigDecimal(num+""));
					pstmt.setInt(4, SysRmbRecTypeEnum.type106.getIntVlue());
					pstmt.setInt(5, SysRmbRecTypeEnum.type106.getIntVlue());
					pstmt.setLong(6, t);
					pstmt.setString(7, "");
					pstmt.setString(8, "");
					pstmt.setBigDecimal(9, CacheClass.toBigDecimal(manowtotalrmb));
					pstmt.setLong(10, CacheClass.getLongValue(sellid));
					pstmt.setInt(11, vrgoodsid);
					pstmt.execute();
					//更新用户缓存
					CrowdUserAuthCache.getInstance().ClearObjectCache(userid+"");
				}
				else if(type==1 && userid>0 && (num-numed)>0) //该寄售为采购,退回未成交金额
				{
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("update cd_user_rmb set nowrmb=(nowrmb+?) where userid=?");
					pstmt.setBigDecimal(1, CacheClass.Bmul_D(sellprice, (num-numed)+""));
					pstmt.setInt(2, userid);
					pstmt.executeUpdate();
					//取更新后的新值
					String nowrmb="0";
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("select nowrmb from cd_user_rmb where userid=?");
					pstmt.setInt(1, userid);
					rs=pstmt.executeQuery();
					if(rs.next())
					{
						nowrmb=rs.getString("nowrmb");
					}
					//增加金额变动记录
					DbConnectionManager.fastcloseStmt(rs,pstmt);
					pstmt = con.prepareStatement("INSERT INTO cd_sys_rmb (userid,payuserid,rmb,level,type,time,name,image,nowrmb) VALUES (?,?,?,?,?,?,?,?,?)");
					pstmt.setInt(1, userid);
					pstmt.setInt(2, 0);
					pstmt.setBigDecimal(3, CacheClass.Bmul_D(sellprice, (num-numed)+""));
					pstmt.setInt(4, SysRmbRecTypeEnum.type12.getIntVlue());
					pstmt.setInt(5, SysRmbRecTypeEnum.type12.getIntVlue());
					pstmt.setLong(6, t);
					pstmt.setString(7, "");
					pstmt.setString(8, "");
					pstmt.setBigDecimal(9, CacheClass.toBigDecimal(nowrmb));
					pstmt.execute();
					//更新用户缓存
					CrowdUserAuthCache.getInstance().ClearObjectCache(userid+"");
				}
			}
			
			//对待付款超过2*24小时的订单进行自动取消订单  0 取消 1待付款
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_sys_order set state=0,canceltime=? where state=1 and  ctime<?;");
			pstmt.setLong(1, t);
			pstmt.setLong(2, t-2*24*3600*1000l);
			pstmt.execute();
			
		
			//统计排名
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("UPDATE cd_user_rmb cr,(SELECT t.userid,(@rowNum4:=@rowNum4+1) as rank from (select * from cd_user_rmb Order by prestigevalue desc ,userid asc) t ,(Select (@rowNum4 :=0) ) b) r " +
					" set cr.rank=r.rank,cr.rankprestigevalue=cr.prestigevalue where cr.userid=r.userid;");
			pstmt.executeUpdate();
			
			//用户vip期排查并更新
			int todaydate=CacheClass.getDateAfterDay2(new Date(), 0);
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_user_auth set viplevel=0,vipdate=0 where vipdate>0 and vipdate<?");
			pstmt.setInt(1, todaydate);
			pstmt.executeUpdate();			
			
			
			//统计　分公司 每日的销量与收益　
			List<analy_smallcompany> list=new ArrayList<analy_smallcompany>();
			todaydate=CacheClass.getDateBeforeDay2_Int(new Date(), 1);//前一天
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select smallcompanyid,sum(buynum) as tbuynum,sum(buyrmb) as tbuyrmb,sum(cutrmb) as tcutrmb from cd_sys_smallcompanylog where ymd=? GROUP BY smallcompanyid;");
			pstmt.setInt(1, todaydate);
			rs=pstmt.executeQuery();	
			while(rs.next())
			{
				analy_smallcompany obj=new analy_smallcompany();
				obj.smallcompanyid=rs.getInt("smallcompanyid");
				obj.buynum=rs.getInt("tbuynum");
				obj.buyrmb=rs.getString("tbuyrmb");
				obj.cutrmb=rs.getString("tcutrmb");
				list.add(obj);
			}
			
			for(analy_smallcompany obj:list)
			{
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("insert into cd_analy_smallcompany (today,smallcompanyid,todaysalesnum,todaysalesrmb,todaycutrmb) values (?,?,?,?,?);");
				pstmt.setInt(1, todaydate);
				pstmt.setInt(2, obj.smallcompanyid);
				pstmt.setInt(3, obj.buynum);
				pstmt.setBigDecimal(4, CacheClass.toBigDecimal(obj.buyrmb));
				pstmt.setBigDecimal(5, CacheClass.toBigDecimal(obj.cutrmb));
				pstmt.execute();
			}

			
			//统计　商城每日　的购物金额（以支付时间为准），提现金额（以提现成功时间为准）
			String inRmb="0";
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select sum(totalrmb) as inRmb from cd_sys_order where ctime>=? and ctime<? and state>1 and paytype!=3;");
			pstmt.setLong(1, t-1*24*3600*1000l);
			pstmt.setLong(2, t);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				inRmb=rs.getString("inRmb");
			}
			
			String outRmb="0";
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select sum(rmb) as outRmb from cd_sys_rmbwithdraw where state=2 and finishtime>=? and finishtime<? ;");
			pstmt.setLong(1, t-1*24*3600*1000l);
			pstmt.setLong(2, t);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				outRmb=rs.getString("outRmb");
			}
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("insert into cd_analy_inoutfound (today,buyrmb,withdrawrmb) values (?,?,?);");
			pstmt.setInt(1, todaydate);
			pstmt.setBigDecimal(2, CacheClass.toBigDecimal(inRmb));
			pstmt.setBigDecimal(3, CacheClass.toBigDecimal(outRmb));
			pstmt.execute();
			
			
			//统计　商城每日　用户总金额(存量资金)、抵押金、可提现金额
			String allRmb="0";
			String priRmb="0";
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select sum(nowrmb) as allRmb,sum(principlelrmb) as priRmb from cd_user_rmb ");
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				allRmb=rs.getString("allRmb");
				priRmb=rs.getString("priRmb");
			}
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("insert into cd_analy_usertotalrmb (today,nowrmb,principlelrmb) values (?,?,?);");
			pstmt.setInt(1, todaydate);
			pstmt.setBigDecimal(2, CacheClass.toBigDecimal(allRmb));
			pstmt.setBigDecimal(3, CacheClass.toBigDecimal(priRmb));
			pstmt.execute();
			
			//对超过签收时间的自动签收
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_sys_order set state=6,arrivetime=?,issys=0 where state=5 and (couriertime+finishdays*24*3600*1000)<?;");
			pstmt.setLong(1, t);
			pstmt.setLong(2, t);
			pstmt.execute();
			
			
			//销量统计
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}

		
	}
	
	
	
	/** 定时任务  每1分执行一次  对银联订单时行主动查询**/
	public void AutoCheckCdOrder2()
	{
	
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			/*//对待取消订单超过7*24小时的订单删除 
			long t=startDate.getTime();
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("DELETE from cd_sys_order where state=0 and ctime<?;");
			pstmt.setLong(1, t-7*24*3600*1000l);
			pstmt.execute();
			
			//对待付款超过2*24小时的订单进行自动取消订单  0 取消 1待付款
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select * cd_sys_order set state=0 where state=1 and  ctime<?;");
			pstmt.setLong(1, t-2*24*3600*1000l);
			pstmt.execute();
			
			
			//统计排名
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			
			pstmt = con.prepareStatement("UPDATE cd_user_rmb cr,(SELECT t.userid,(@rowNum4:=@rowNum4+1) as rank from (select * from cd_user_rmb Order by totalinvestrmb desc ,userid asc) t ,(Select (@rowNum4 :=0) ) b) r " +
					" set cr.rank=r.rank where cr.userid=r.userid;");

			pstmt = con.prepareStatement("UPDATE cd_user_rmb cr,(SELECT t.userid,(@rowNum4:=@rowNum4+1) as rank from (select * from cd_user_rmb Order by prestigevalue desc ,userid asc) t ,(Select (@rowNum4 :=0) ) b) r " +
					" set cr.rank=r.rank where cr.userid=r.userid;");
			pstmt.executeUpdate();
			
			//用户vip期排查并更新
			int todaydate=CacheClass.getDateAfterDay2(new Date(), 0);
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("update cd_user_auth set vipdate=0 where vipdate>0 and vipdate<?");
			pstmt.setInt(1, todaydate);
			pstmt.executeUpdate();			
			
			
			//统计　分公司 每日的销量与收益　
			List<analy_smallcompany> list=new ArrayList<analy_smallcompany>();
			todaydate=CacheClass.getDateBeforeDay2_Int(new Date(), 1);//前一天
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select smallcompanyid,sum(buynum) as tbuynum,sum(buyrmb) as tbuyrmb,sum(cutrmb) as tcutrmb from cd_sys_smallcompanylog where ymd=? GROUP BY smallcompanyid;");
			pstmt.setInt(1, todaydate);
			rs=pstmt.executeQuery();	
			while(rs.next())
			{
				analy_smallcompany obj=new analy_smallcompany();
				obj.smallcompanyid=rs.getInt("smallcompanyid");
				obj.buynum=rs.getInt("tbuynum");
				obj.buyrmb=rs.getString("tbuyrmb");
				obj.cutrmb=rs.getString("tcutrmb");
				list.add(obj);
			}
			
			for(analy_smallcompany obj:list)
			{
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("insert into cd_analy_smallcompany (today,smallcompanyid,todaysalesnum,todaysalesrmb,todaycutrmb) values (?,?,?,?,?);");
				pstmt.setInt(1, todaydate);
				pstmt.setInt(2, obj.smallcompanyid);
				pstmt.setInt(3, obj.buynum);
				pstmt.setBigDecimal(4, CacheClass.toBigDecimal(obj.buyrmb));
				pstmt.setBigDecimal(5, CacheClass.toBigDecimal(obj.cutrmb));
				pstmt.execute();
			}

			
			//统计　商城每日　的购物金额（以支付时间为准），提现金额（以提现成功时间为准）
			String inRmb="0";
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select sum(totalrmb) as inRmb from cd_sys_order where ctime>=? and ctime<? and state>1 and paytype!=3;");
			pstmt.setLong(1, t-1*24*3600*1000l);
			pstmt.setLong(2, t);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				inRmb=rs.getString("inRmb");
			}
			
			String outRmb="0";
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select sum(rmb) as outRmb from cd_sys_rmbwithdraw where state=2 and finishtime>=? and finishtime<? ;");
			pstmt.setLong(1, t-1*24*3600*1000l);
			pstmt.setLong(2, t);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				outRmb=rs.getString("outRmb");
			}
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("insert into cd_analy_inoutfound (today,buyrmb,withdrawrmb) values (?,?,?);");
			pstmt.setInt(1, todaydate);
			pstmt.setBigDecimal(2, CacheClass.toBigDecimal(inRmb));
			pstmt.setBigDecimal(3, CacheClass.toBigDecimal(outRmb));
			pstmt.execute();
			
			
			//统计　商城每日　用户总金额(存量资金)、抵押金、可提现金额
			String allRmb="0";
			String priRmb="0";
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select sum(nowrmb) as allRmb,sum(principlelrmb) as priRmb from cd_user_rmb ");
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				allRmb=rs.getString("allRmb");
				priRmb=rs.getString("priRmb");
			}
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("insert into cd_analy_usertotalrmb (today,nowrmb,principlelrmb) values (?,?,?);");
			pstmt.setInt(1, todaydate);
			pstmt.setBigDecimal(2, CacheClass.toBigDecimal(allRmb));
			pstmt.setBigDecimal(3, CacheClass.toBigDecimal(priRmb));
			pstmt.execute();
			
*/
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}

		
	}

	
	
	/** 定时任务  每24小时执行一次  批量交易转入瓶数**/
	public void AutoInVrPreOrder()
	{
		List<QueryJWModeHoldData> list = new ArrayList<QueryJWModeHoldData>();
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			Date now= new Date();
			long t=now.getTime();
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select n.cdvrid,n.userid,n.vrgoodsid,n.num,a.uid  from cdvr_sys_num n" +
					" left join cd_user_auth a on a.userid=n.userid " +
					" where n.state=0 and a.uid is not null;");
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				QueryJWModeHoldData obj= new QueryJWModeHoldData();
				obj.USER_ID=rs.getString("uid");
				obj.PRI=rs.getString("vrgoodsid");
				obj.TPN=rs.getString("num");
				obj.HOLD_ID=rs.getString("cdvrid");
				list.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		if(list.size()>0)
		{
			QueryJWModeHold querData=new QueryJWModeHold(); 
			querData.hold=list;
			String REQName="hold_transfer";
			String jsonStr=JSON.toJSONString(querData);
			String xmlStr=TestXml2Json.jsontoXml(jsonStr);
			xmlStr=xmlStr.replace("<MEBS_MOBILE>", "<MEBS_MOBILE><REQ name=\""+REQName+"\" >");
			xmlStr=xmlStr.replace("</MEBS_MOBILE>", "</REQ></MEBS_MOBILE>");
			xmlStr=xmlStr.replace("<e>", "");xmlStr=xmlStr.replace("</e>", "");
			List<ResultModel4> resultList=null;
			System.out.println(xmlStr);
			try {
				resultList=Api4.buildRequest(CacheClass.getTradeUlr(),xmlStr);
			} catch (Exception e) {
				resultList.clear();
				e.printStackTrace();
			}
			if(resultList==null || resultList.size()!=list.size())
			{
				
			}
			else //注册交易系统成功，用户表追加uid值
			{
				for(ResultModel4 tmp:resultList)
				{
					if(CacheClass.getIntValue(tmp.success)!=0)//失败
					{
						updateVrInOrder(tmp.holdID,false);
					}
					else//成功
					{
						updateVrInOrder(tmp.holdID,true);
					}
				}
			}
		}
	}
	
	
	private void updateVrInOrder(String cdvrid,boolean successflag)
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			Date now= new Date();
			long t=now.getTime();
			
			if(successflag) //成功
			{
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement("update cdvr_sys_num set state=1,utime=? where cdvrid=?;");
				pstmt.setLong(1, t);
				pstmt.setLong(2, CacheClass.getLongValue(cdvrid));
				pstmt.executeUpdate();
			}
			else //失败
			{
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement("update cdvr_sys_num set state=2,utime=? where cdvrid=?;");
				pstmt.setLong(1, t);
				pstmt.setLong(2, CacheClass.getLongValue(cdvrid));
				pstmt.executeUpdate();
				
				
				
				//瓶数退回
				int userid=0;
				float num=0;
				int vrgoodsid=0;
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("select userid,vrgoodsid,num from cdvr_sys_num where cdvrid=?");
				pstmt.setLong(1, CacheClass.getLongValue(cdvrid));
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					userid=rs.getInt("userid");
					vrgoodsid=rs.getInt("vrgoodsid");
					num=rs.getInt("num");
				}
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("update cd_user_num set num =(num +?) where userid=? and vrgoodsid=? ;");
				pstmt.setBigDecimal(1, CacheClass.toBigDecimal(num+""));
				pstmt.setInt(2, userid);
				pstmt.setInt(3, vrgoodsid);
				pstmt.executeUpdate();
				
				//取更新后的新值
				String manowtotalrmb="0";
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("select num from cd_user_num where userid=?  and vrgoodsid=? ;");
				pstmt.setInt(1, userid);
				pstmt.setInt(2, vrgoodsid);
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					manowtotalrmb=rs.getString("num");
				}
				
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("INSERT INTO cd_sys_rmb (userid,payuserid,rmb,level,type,time,name,image,nowrmb,orderid,vrgoodsid) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
				pstmt.setInt(1, userid);
				pstmt.setInt(2, 0);
				pstmt.setBigDecimal(3, CacheClass.toBigDecimal(num+""));
				pstmt.setInt(4, 0);
				pstmt.setInt(5, SysRmbRecTypeEnum.type108.getIntVlue());  
				pstmt.setLong(6, now.getTime());
				pstmt.setString(7, "系统");
				pstmt.setString(8, "");
				pstmt.setBigDecimal(9, CacheClass.toBigDecimal(manowtotalrmb));
				pstmt.setLong(10, CacheClass.getLongValue(cdvrid));
				pstmt.setInt(11, vrgoodsid);
				pstmt.execute();
				
			}

			
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
	}

	
	
	//统计近days天内送vip订单的总金额
	public float Get_OrderVipRmbByUseridDays(int userid,int days) 
	{
		float total=0;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Date now = new Date();
		try {
			
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select sum(totalrmb) as total from cd_sys_order where userid=? and state>1 and viplevel!=0 and starttime>?  ");
			pstmt.setInt(1, userid);
			pstmt.setLong(2, CacheClass.getDateBeforeDay(now, days).getTime() );
			rs=pstmt.executeQuery();
			if (rs.next()) {
				total=rs.getFloat("total");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		return total;
	}
	
	private class analy_smallcompany
	{
		public int smallcompanyid;
		
		public int buynum;
		
		public String buyrmb;
		
		public String cutrmb;

	}
	
	private class analy_vrgoodsprice
	{
		public int vrgoodsid;
		
		public String price;
	}
	
	
}
